TD Récapitulatif SQL
TD révision générale
L3 MIASHS |
| Année 2025 |
Recapitulatif SQL
Utiliser les outils les plus adaptés parmi ceux vus depuis le début de l’année pour répondre aux questions suivantes.
Schéma world
Quel est le pays qui a le plus de villes recensées dans la table world.city? Combien de villes ? (363 villes)
WITH country_city AS
(SELECT name_country , COUNT(name) nbrcity
FROM world.country NATURAL JOIN world.city
GROUP BY name_country)
SELECT name_country, nbrcity
FROM country_city
WHERE nbrcity = (SELECT MAX(nbrcity)
FROM country_city) ;Quels sont les pays qui ont \(3\) ou plus langues officielles ? (8 lignes)
SELECT name_country
FROM world.country NATURAL JOIN world.countrylanguage
WHERE isofficial
GROUP BY name_country HAVING COUNT(*) > 2 ;Quels sont les pays dont plus de \(1.000.000\) d’habitants parlent une langue non officielle ? (72 lignes)
SELECT DISTINCT name_country
FROM world.country NATURAL JOIN world.countrylanguage
WHERE (NOT isofficial) AND
(percentage / 100) * population_country >= 1000000 ;Quelles sont les langues qui sont officielles dans tous les continents où au moins une langue est parlée ? (1 ligne)
WITH R AS
(SELECT count(DISTINCT continent)
FROM world.country NATURAL JOIN world.countrylanguage)
SELECT language, count(DISTINCT continent) as nbcon
FROM world.country NATURAL JOIN world.countrylanguage WHERE isofficial
GROUP BY language
HAVING count(DISTINCT continent) in (SELECT * FROM R)
ORDER BY nbcon DESC ;ou
WITH continent_language AS
(SELECT DISTINCT language , continent
FROM country NATURAL JOIN countrylanguage
WHERE isofficial),
all_continent_language AS
(SELECT DISTINCT l.language , c.continent
FROM countrylanguage l , continent_language c),
not_continent_language AS
((SELECT * FROM all_continent_language)
EXCEPT
(SELECT * FROM continent_language))
SELECT DISTINCT language
FROM countrylanguage
WHERE language NOT IN (SELECT language FROM not_continent_language) ;Quels sont les pays pour lesquels une langue non officielle est parlée par strictement plus de monde (dans le pays) qu’il n’y a d’habitants dans la capitale ? (118 lignes)
SELECT DISTINCT name_country
FROM (country c JOIN city ON id = capital)
JOIN countrylanguage l ON c.countrycode = l.countrycode
WHERE NOT isofficial
AND percentage / 100 * population_country > population ;Quels est le nombre de villes par region ?
SELECT region , COUNT(id) AS nbr_city
FROM country NATURAL JOIN city
GROUP BY region ;Schéma pagila
Quel est l’acteur (actor_id) présent sur le plus de DVD (inventory_id) ?
WITH actor_dvd AS
(SELECT actor_id , count(inventory_id) nbrdvd
FROM actor NATURAL JOIN film_actor NATURAL JOIN inventory
GROUP BY actor_id)
SELECT first_name , last_name
FROM actor NATURAL JOIN actor_dvd
WHERE nbrdvd = (SELECT MAX(nbrdvd) FROM actor_dvd) ;Quel est le prix moyen d’une location de DVD ?
SELECT AVG(amount)
FROM payment ;Quel est le DVD qui a rapporté le plus d’argent ? Le film (film_id)?
Pour le DVD:
WITH inventory_amount AS
(SELECT inventory_id , SUM(amount) AS dollar
FROM inventory NATURAL JOIN rental NATURAL JOIN payment
GROUP BY inventory_id)
SELECT inventory_id , dollar
FROM inventory_amount
WHERE dollar >= ALL (SELECT dollar FROM inventory_amount) ;Et pour le film:
WITH film_amount AS
(SELECT film_id , SUM(amount) AS dollar
FROM inventory NATURAL JOIN rental NATURAL JOIN payment
GROUP BY film_id)
SELECT film_id , dollar
FROM inventory_amount
WHERE dollar >= ALL (SELECT dollar FROM film_amount) ;Quels sont les employés (staff_id) qui travaillent dans une autre ville que celle dans laquelle ils vivent ? (4 lignes)
WITH staff_maison AS
(SELECT staff_id , city_id
FROM (staff NATURAL JOIN address)),
staff_travail AS
(SELECT staff_id , city_id
FROM (staff p JOIN store s ON p.store_id = s.store_id) , address a
WHERE a.address_id = s.address_id)
SELECT staff_id FROM staff_travail NATURAL JOIN staff_maison ;